﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using FortuneCookie.EPiServer.SearchAndReplace.Configuration;

namespace FortuneCookie.EPiServer.SearchAndReplace.Data
{

    /// <summary>
    /// SqlDataHelper class
    /// </summary>
    internal sealed class SqlDataHelper : DataAccessBase, IDataHelper
    {

        #region Constructor

        /// <summary>
        /// Default constructor
        /// </summary>
        public SqlDataHelper()
        {
            if (ConfigurationManager.ConnectionStrings[ConfigurationHelper.ConnectionStringName] == null)
                throw new ApplicationException(string.Format("The connection string '{0}' could not be found", ConfigurationHelper.ConnectionStringName));

            Connection = new SqlConnection(ConfigurationManager.ConnectionStrings[ConfigurationHelper.ConnectionStringName].ConnectionString);
        }

        #endregion Constructor

        #region Methods

        /// <summary>
        /// Gets all search and replace entities
        /// </summary>
        /// <returns></returns>
        public List<SearchAndReplace> GetAll()
        {
            return GetSearchAndReplaces("usp_sar_SearchAndReplaceGetAll", null);
        }

        /// <summary>
        /// Gets a search and replace entity by it's id
        /// </summary>
        /// <param name="id">Id</param>
        /// <returns></returns>
        public SearchAndReplace GetById(Guid id)
        {
            List<DbParameter> parameters = new List<DbParameter> { new SqlParameter("@Id", id) };
            List<SearchAndReplace> searchAndReplaces = GetSearchAndReplaces("usp_sar_SearchAndReplaceGetById", parameters);
            return (searchAndReplaces.Count == 0 ? null : searchAndReplaces[0]);
        }

        /// <summary>
        /// Gets search and replace entities
        /// </summary>
        /// <param name="procedureName">Procedure name</param>
        /// <param name="parameters">Parameters</param>
        /// <returns></returns>
        private List<SearchAndReplace> GetSearchAndReplaces(string procedureName, List<DbParameter> parameters)
        {
            List<SearchAndReplace> searchAndReplaces = Load<List<SearchAndReplace>>(procedureName, parameters, BuildSearchAndReplaces);

            foreach (SearchAndReplace searchAndReplace in searchAndReplaces)
            {
                parameters = new List<DbParameter> { new SqlParameter("@SearchAndReplaceId", searchAndReplace.Id) };
                List<Page> pages = Load<List<Page>>("usp_sar_PageGetBySearchAndReplaceId", parameters, BuildPages);
                searchAndReplace.Pages.AddRange(pages);

                parameters = new List<DbParameter> { new SqlParameter("@SearchAndReplaceId", searchAndReplace.Id) };
                List<DynamicProperty> dynamicProperties = Load<List<DynamicProperty>>("usp_sar_DynamicPropertyGetBySearchAndReplaceId", parameters, BuildDynamicProperties);
                searchAndReplace.DynamicProperties.AddRange(dynamicProperties);

                parameters = new List<DbParameter> { new SqlParameter("@SearchAndReplaceId", searchAndReplace.Id) };
                List<Error> errors = Load<List<Error>>("usp_sar_ErrorGetBySearchAndReplaceId", parameters, BuildErrors);
                searchAndReplace.Errors.AddRange(errors);
            }

            return searchAndReplaces;
        }

        /// <summary>
        /// Updates a search and replace entity
        /// </summary>
        /// <param name="searchAndReplace"></param>
        public void Update(SearchAndReplace searchAndReplace)
        {
            if (!searchAndReplace.Existing || searchAndReplace.HasChanges)
            {
                List<DbParameter> parameters = new List<DbParameter>
                                                   {
                                                       new SqlParameter("@Id", searchAndReplace.Id),
                                                       new SqlParameter("@StartPage", searchAndReplace.StartPage),
                                                       new SqlParameter("@Created", searchAndReplace.Created),
                                                       new SqlParameter("@CreatedBy", searchAndReplace.CreatedBy),
                                                       new SqlParameter("@Find", searchAndReplace.Find),
                                                       new SqlParameter("@Replace", searchAndReplace.Replace),
                                                       new SqlParameter("@MatchCase", searchAndReplace.MatchCase),
                                                       new SqlParameter("@UpdateDynamicProperties", searchAndReplace.UpdateDynamicProperties),
                                                       new SqlParameter("@SearchWithinWords", searchAndReplace.SearchWithinWords),
                                                       new SqlParameter("@StartPageOnly", searchAndReplace.StartPageOnly),
                                                       new SqlParameter("@LanguageBranch", searchAndReplace.LanguageBranch),
                                                       new SqlParameter("@Completed", searchAndReplace.Completed)
                                                   };

                ExecuteNonQuery("usp_sar_SearchAndReplace" + (searchAndReplace.Existing ? "Update" : "Insert"), parameters);
                searchAndReplace.Existing = true;
                searchAndReplace.HasChanges = false;
            }

            foreach (Page page in searchAndReplace.Pages.Where(current => !current.Existing || current.HasChanges))
            {
                List<DbParameter> parameters = new List<DbParameter>
                                 {
                                     new SqlParameter("@Id", page.Id),
                                     new SqlParameter("@SearchAndReplaceId", page.SearchAndReplaceId),
                                     new SqlParameter("@PageId", page.PageId),
                                     new SqlParameter("@WorkId", page.WorkId),
                                     new SqlParameter("@PreviousWorkId", page.PreviousWorkId),
                                     new SqlParameter("@LanguageBranch", page.LanguageBranch),
                                     new SqlParameter("@SortIndex", page.SortIndex)
                                 };

                ExecuteNonQuery("usp_sar_Page" + (page.Existing ? "Update" : "Insert"), parameters);
                page.Existing = true;
                page.HasChanges = false;
            }

            foreach (DynamicProperty dynamicProperty in searchAndReplace.DynamicProperties.Where(current => !current.Existing || current.HasChanges))
            {
                List<DbParameter> parameters = new List<DbParameter>
                                 {
                                     new SqlParameter("@Id", dynamicProperty.Id),
                                     new SqlParameter("@SearchAndReplaceId", dynamicProperty.SearchAndReplaceId),
                                     new SqlParameter("@PageId", dynamicProperty.PageId),
                                     new SqlParameter("@WorkId", dynamicProperty.WorkId),
                                     new SqlParameter("@LanguageBranch", dynamicProperty.LanguageBranch),
                                     new SqlParameter("@PropertyName", dynamicProperty.PropertyName),
                                     new SqlParameter("@SortIndex", dynamicProperty.SortIndex)
                                 };

                ExecuteNonQuery("usp_sar_DynamicProperty" + (dynamicProperty.Existing ? "Update" : "Insert"), parameters);
                dynamicProperty.Existing = true;
                dynamicProperty.HasChanges = false;
            }

            foreach (Error error in searchAndReplace.Errors.Where(current => !current.Existing || current.HasChanges))
            {
                List<DbParameter> parameters = new List<DbParameter>
                                 {
                                     new SqlParameter("@Id", error.Id),
                                     new SqlParameter("@SearchAndReplaceId", error.SearchAndReplaceId),
                                     new SqlParameter("@PageId", error.PageId),
                                     new SqlParameter("@WorkId", error.WorkId),
                                     new SqlParameter("@LanguageBranch", error.LanguageBranch),
                                     new SqlParameter("@PropertyName", error.PropertyName),
                                     new SqlParameter("@ErrorMessage", error.ErrorMessage),
                                     new SqlParameter("@PageProperty", error.PageProperty),
                                     new SqlParameter("@SortIndex", error.SortIndex)
                                 };

                ExecuteNonQuery("usp_sar_Error" + (error.Existing ? "Update" : "Insert"), parameters);
                error.Existing = true;
                error.HasChanges = false;
            }
        }

        /// <summary>
        /// Deletes a search and replace entity
        /// </summary>
        /// <param name="searchAndReplace"></param>
        public void Delete(SearchAndReplace searchAndReplace)
        {
            List<DbParameter> parameters;

            foreach (Page page in searchAndReplace.Pages)
            {
                parameters = new List<DbParameter> { new SqlParameter("@Id", page.Id) };
                ExecuteNonQuery("usp_sar_PageDelete", parameters);
            }

            foreach (DynamicProperty dynamicProperty in searchAndReplace.DynamicProperties)
            {
                parameters = new List<DbParameter> { new SqlParameter("@Id", dynamicProperty.Id) };
                ExecuteNonQuery("usp_sar_DynamicPropertyDelete", parameters);
            }

            foreach (Error error in searchAndReplace.Errors)
            {
                parameters = new List<DbParameter> { new SqlParameter("@Id", error.Id) };
                ExecuteNonQuery("usp_sar_ErrorDelete", parameters);
            }

            parameters = new List<DbParameter> { new SqlParameter("@Id", searchAndReplace.Id) };
            ExecuteNonQuery("usp_sar_SearchAndReplaceDelete", parameters);
        }

        /// <summary>
        /// Builds search and replace entities
        /// </summary>
        /// <param name="reader"></param>
        /// <returns></returns>
        private List<SearchAndReplace> BuildSearchAndReplaces(IDataReader reader)
        {
            List<SearchAndReplace> searchAndReplaces = new List<SearchAndReplace>();

            while (reader.Read())
            {
                SearchAndReplace searchAndReplace = new SearchAndReplace
                                {
                                    Id = reader.GetGuid(0),
                                    StartPage = reader.GetString(1),
                                    Created = reader.GetDateTime(2),
                                    CreatedBy = reader.GetString(3),
                                    Find = reader.GetString(4),
                                    Replace = reader.GetString(5),
                                    MatchCase = reader.GetBoolean(6),
                                    UpdateDynamicProperties = reader.GetBoolean(7),
                                    SearchWithinWords = reader.GetBoolean(8),
                                    StartPageOnly = reader.GetBoolean(9),
                                    LanguageBranch = reader.GetString(10),
                                    Completed = reader.GetBoolean(11),
                                    Existing = true,
                                    HasChanges = false
                                };

                searchAndReplaces.Add(searchAndReplace);
            }

            return searchAndReplaces;
        }

        /// <summary>
        /// Builds page entities
        /// </summary>
        /// <param name="reader"></param>
        /// <returns></returns>
        private List<Page> BuildPages(IDataReader reader)
        {
            List<Page> pages = new List<Page>();

            while (reader.Read())
            {
                Page page = new Page
                                {
                                    Id = reader.GetGuid(0),
                                    SearchAndReplaceId = reader.GetGuid(1),
                                    PageId = reader.GetInt32(2),
                                    WorkId = reader.GetInt32(3),
                                    PreviousWorkId = reader.GetInt32(4),
                                    LanguageBranch = reader.GetString(5),
                                    SortIndex = reader.GetInt32(6),
                                    Existing = true,
                                    HasChanges = false
                                };

                pages.Add(page);
            }

            return pages;
        }

        /// <summary>
        /// Builds dynamic property entities
        /// </summary>
        /// <param name="reader"></param>
        /// <returns></returns>
        private List<DynamicProperty> BuildDynamicProperties(IDataReader reader)
        {
            List<DynamicProperty> dynamicProperties = new List<DynamicProperty>();

            while (reader.Read())
            {
                DynamicProperty page = new DynamicProperty
                {
                    Id = reader.GetGuid(0),
                    SearchAndReplaceId = reader.GetGuid(1),
                    PageId = reader.GetInt32(2),
                    WorkId = reader.GetInt32(3),
                    LanguageBranch = reader.GetString(4),
                    PropertyName = reader.GetString(5),
                    SortIndex = reader.GetInt32(6),
                    Existing = true,
                    HasChanges = false
                };

                dynamicProperties.Add(page);
            }

            return dynamicProperties;
        }

        /// <summary>
        /// Builds error entities
        /// </summary>
        /// <param name="reader"></param>
        /// <returns></returns>
        private List<Error> BuildErrors(IDataReader reader)
        {
            List<Error> errors = new List<Error>();

            while (reader.Read())
            {
                Error error = new Error
                {
                    Id = reader.GetGuid(0),
                    SearchAndReplaceId = reader.GetGuid(1),
                    PageId = reader.GetInt32(2),
                    WorkId = reader.GetInt32(3),
                    LanguageBranch = reader.GetString(4),
                    PropertyName = reader.GetString(5),
                    ErrorMessage = reader.GetString(6),
                    PageProperty = reader.GetBoolean(7),
                    SortIndex = reader.GetInt32(8),
                    Existing = true,
                    HasChanges = false
                };

                errors.Add(error);
            }

            return errors;
        }

        /// <summary>
        /// Checks whether dependant tables exist
        /// </summary>
        /// <returns></returns>
        public bool TablesExist()
        {
            return ExecuteScalar<int>("SELECT COUNT(*) FROM [sysobjects] WHERE [name] = 'sar_Page'") == 1;
        }

        /// <summary>
        /// Creates new objects in the database based on the supplied script
        /// </summary>
        /// <param name="script"></param>
        public void CreateObjects(string script)
        {
            ExecuteNonQuery(script);
        }

        #endregion Methods

  }
}
